''' 

''' Calls custom stored procedure.
''' 
Public Sub CallCustomStoredProcedure()
	'' Step 1: Configure parameters for your stored procedure if the stored procedure accepts parameters.
	'' Each database type has different parameter type.
	'' Please change them accordingly to fit your application's logic.
	'' SQL Server: 
	''	Parameter type: System.Data.SqlDbType
	'' Oracle:
	''	Parameter type: System.Data.OracleClient.OracleType
	'' MySql:
	'' 	Parameter type: MySql.Data.MySqlClient.MySqlDbType
	
	'Dim firstParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
	
	'' For SQL Server: use parameter type System.Data.SqlDbType
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, System.Data.SqlDbType.Int, System.Data.ParameterDirection.Input)

	'' For Oracle: use parameter type System.Data.OracleClient.OracleType	
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("pk_EmployeeID", 5, System.Data.OracleClient.OracleType.Int, System.Data.ParameterDirection.Input)

	'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
	'firstParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeID", 5, MySql.Data.MySqlClient.MySqlDbType.Int, System.Data.ParameterDirection.Input, True)


	'Dim secondParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
	
	'' For SQL Server: use parameter type System.Data.SqlDbType
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Input)    
	
	'' For Oracle: use parameter type System.Data.OracleClient.OracleType
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("pk_EmployeeLastName", "Smith", System.Data.OracleClient.OracleType.VarChar, System.Data.ParameterDirection.Input)    

	'' For MySql: use parameter type MySql.Data.MySqlClient.MySqlDbType
	'secondParameter = New BaseClasses.Data.StoredProcedureParameter("@pk_EmployeeLastName", "Smith", MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input, True)    


	'' Step 2: Add the configured parameters to an array list.
	'Dim parameterList(2) As BaseClasses.Data.StoredProcedureParameter
	'parameterList(0) = firstParameter
	'parameterList(1) = secondParameter
  '' Note, in case you need to return output value from the Stored procedure you might use the following code:
  '' Dim thirdParameter As BaseClasses.Data.StoredProcedureParameter = Nothing
  '' Dim myvar As String = ""
  '' thirdParameter = New BaseClasses.Data.StoredProcedureParameter("@StatusDate", myvar, System.Data.SqlDbType.VarChar, System.Data.ParameterDirection.Output)
  '' NOTE: if this parameter has a string type such as varchar, nvarchar, string, etc. you have to set its size:
  '' thirdParameter.Size = 10
  '' parameterList(2) = thirdParameter
	
  '' Skip steps 1 and 2 if stored procedure does not accept parameters.

	Dim myStoredProcedure As BaseClasses.Data.StoredProcedure = Nothing

	'' "DatabaseNorthwind1" is a connection string obtained from Web.config 
	'' located in application's root directory.

	'' Step 3: Connect to the stored procedure using following line if 
	'' the stored procedure does not accept any parameters
	myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", nothing)

	'' If the stored procedure accepts parameters, use the following
	'myStoredProcedure = New BaseClasses.Data.StoredProcedure("DatabaseNorthwind1", "Stored_Procedure_Name", parameterList)    
    

	'' Step 4: Run the stored procedure.    
	'' RunQuery() will return true if stored procedure successfully executed. Otherwise, it will return false.
	'' Use RunQuery or RunNonQuery.  RunQuery is used when a set of records is being returned.  RunNonQuery is used when one or more values are returned through output parameters.
	If (myStoredProcedure.RunQuery()) Then
    '' If you have output parameter, use code like:
    '' Dim outputParameter As System.Data.IDataParameter
    '' outputParameter = DirectCast(myStoredProcedure.OutputParameters(0), System.Data.IDataParameter)
    '' Dim myText As String = outputParameter.Value.ToString()
    
		'' Result from stored procedure is available 
		'' as a DataSet or as an array of RecordValue objects.
		
		' return (myStoredProcedure.DataSet)
		' return (myStoredProcedure.Records)		

		'' If you want to go through the data set and access each row and column,
		'' see below
		' Dim ds As System.Data.DataSet = myStoredProcedure.DataSet
		' Dim dt As System.Data.DataTable = ds.Tables.Item(0)
		' Dim myRow As System.Data.DataRow
		' For Each myRow In dt.Rows
			' Dim lastName as string = myRow.Item(lastNameColumnIndex).ToString()	
		' Next

		'' If you want to go through the record set and access each row and column,
		'' see below	
		' Dim records As ArrayList = myStoredProcedure.Records
		' Dim record As BaseClasses.Data.RecordValue
		' For Each record In records
			' Dim lastName as string = record.ColumnValues(lastNameColumnIndex).Value.ToString()
		' Next record
	Else
		'' You can raise an exception in the custom stored procedure and catch the exception and reporting it to the user.
		'' To raise the exception:
		
		'' SET NOCOUNT ON;
	    	'' RAISERROR (N'My custom error message goes here', 11, 1)
	
		'' IMPORTANT: If you raise an error that has a severity level of 10 or less, it is considered  
		'' a warning, and no exception is raised. The severity of the error must be between 11 and 20
		'' for an exception to be thrown.
	
		'' Once the exception is raised, you can look at:
		'' myStoredProcedure.ErrorMessage to get the text of the error message and use RegisterJScriptAlert to report this to the user.
	End If   
End Sub